package com.sec.etech.form.cust.service;

public class EtechTaskSqlHelper {

    public static String getBpmFlowTasks(String bpmExt, boolean withBizId){
        String sql = null;
        if(withBizId){
            sql = "select t.*,l.biz_id_" +
                    " , (select group_concat(pl.identity_ SEPARATOR ';') from bpm_task_identitylink pl where pl.task_id_=t.id_ and pl.type_='user') as assignee_ids_ " +
                    " from bpm_task t, bpm_bus_link l " +
                    " where t.inst_id_=l.inst_id_ " +
                    "  order by l.biz_id_, t.create_time_ asc  ";

        }else {
            sql = "select t.inst_id_, t.assignee_id_" +
                    " , (select group_concat(pl.identity_ SEPARATOR ';') from bpm_task_identitylink pl where pl.task_id_=t.id_ and pl.type_='user') as assignee_ids_ " +
                    " ,o.* " +
                    " from bpm_task t, bpm_task_opinion o " +
                    " where t.task_id_=o.task_id_  " +
                    " order by t.inst_id_, t.create_time_ asc ";
        }
        return  sql;
    }

    public static String getBpmTaskSqlByExtType(String bpmExt) {
        return getBpmTaskSqlByExtType(bpmExt, true);
    }

    public static String getBpmTaskSqlByExtType(String bpmExt, boolean withBizId) {
        if(bpmExt==null){
            return null;
        }
        // 任务查询SQL
        String taskSql = null;
        // 流程扩展，无task sql
        if ("bpm_flow_ext".equalsIgnoreCase(bpmExt)) {
            return taskSql;
            //原有定制机制，无task sql
        } else if ("bpm_task_ext".equalsIgnoreCase(bpmExt)) {
            return taskSql;
        }

        if(bpmExt.startsWith("todotask_")){
            StringBuffer sqlSb = new StringBuffer();
            sqlSb = sqlSb.append("SELECT DISTINCT t.task_id_,t.name_,t.subject_,t.inst_id_,t.node_id_,t.assignee_id_,t.create_time_");
            sqlSb = sqlSb.append(",( SELECT group_concat( pl.identity_ SEPARATOR ';' ) FROM bpm_task_identitylink pl WHERE pl.task_id_ = t.id_ AND pl.type_ = 'user' ) AS assignee_ids_");
            if(withBizId){
                sqlSb = sqlSb.append(",b.biz_id_,b.biz_code_");
            }
            sqlSb = sqlSb.append(",o.def_key_,o.def_name_,o.remain_days_,o.master_group_,o.master_group_name_,o.plan_workdays_,o.plan_complete_day_,o.node_tip_");
            sqlSb = sqlSb.append(" FROM bpm_task t");
            if(withBizId){
                sqlSb = sqlSb.append(" JOIN bpm_bus_link b ON t.inst_id_ = b.inst_id_ ");
            }
            sqlSb = sqlSb.append(" LEFT JOIN bpm_task_identitylink i ON t.id_ = i.task_id_ ")
                    .append(" LEFT JOIN bpm_task_opinion o ON t.id_ = o.task_id_ ");
            String todotaskSql = sqlSb.toString();

            //我的待处理
            if ("todotask_my".equalsIgnoreCase(bpmExt)) {
                taskSql = todotaskSql +
                        "WHERE (\n" +
                        "\t\tt.assignee_id_ = '${currentUserId}' \n" +
                        "\t  OR ( t.assignee_id_ = '0' AND i.permission_code_ IN ( '${curUserPermCodes}' ) ) \n" +
                        "\t)";
            }
            //班组待处理
            //班组code改为班组id
            if ("todotask_group".equalsIgnoreCase(bpmExt)) {
                taskSql = todotaskSql +
                        "WHERE o.master_group_ IN (\n" +
                        "\t'${currentMasterGroupIds}')";
            }
            // 全部处理中
            if ("todotask_all".equalsIgnoreCase(bpmExt)) {
                taskSql = todotaskSql;

            }
            taskSql += " order by o.plan_complete_day_ ";

            return taskSql;
        }

        //我的已处理
        if ("dealtask_my".equalsIgnoreCase(bpmExt)) {
            // 兼容bpe旧流程已处理历史，去掉我的已处理关联流程表
            taskSql = "SELECT t.task_id_,t.task_name_,t.inst_id_";
            if(withBizId){
                taskSql += ",b.biz_id_";
                //"\tb.biz_code_, \n" +
            }
            taskSql +=
                    "\t,t.is_in_time_,t.node_tip_,\n" +
                    "\tt.opinion_,\n" +
                    "\tt.approve_time_,\n" +
                    "\tt.plan_complete_day_\n" +  //计划完成日期
                    "FROM\n" +
                    "\tbpm_task_opinion t\n"+
                    "LEFT JOIN bpm_task t2 ON t.task_id_ = t2.id_  ";
            if(withBizId){
                taskSql += "\tINNER JOIN bpm_bus_link b ON t.inst_id_ = b.inst_id_ \n";
            }
            taskSql += "WHERE\n" +
                    "\tt.status_ NOT IN ( 'start', 'skip', 'end' ) \n" +
                    "\tAND t.approver_ = '${currentUserId}'\n  order by t.approve_time_ desc";
        }
        return taskSql;
    }
}
